package fingerStore;

/**
 * Created by mengwei666 on 2016/11/5.
 */

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBOpener {
    public static final String url = "jdbc:mysql://127.0.0.1/db_songfinger?useSSL=true";
    public static final String name = "com.mysql.jdbc.Driver";
    public static final String user = "root";
    public static final String password = "dp85691138";

    public Connection conn = null;
    public PreparedStatement pst = null;

    public Connection getConn() {
        Connection conn = null;
        try {
            Class.forName(name); //classLoader,加载对应驱动
            conn = (Connection) DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }


    public void close() {
        try {
            this.conn.close();
            this.pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public int insertFinger(ShazamHash hash) {
        Connection conn = getConn();
        int i = 0;
        String sql = "insert into finger (f1,f2,dt) values(?,?,?)";
        try {
            pst = (PreparedStatement) conn.prepareStatement(sql);
            pst.setShort(1, hash.f1);
            pst.setShort(2, hash.f2);
            pst.setShort(3, hash.dt);
            i += pst.executeUpdate();
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            pst = null;
            conn = null;
        }
        return i;
    }

    public int insertSong(String name) {
        Connection conn = getConn();
        int i = 0;
        String sql = "insert into song(name) values(?)";
        try {
            pst = (PreparedStatement) conn.prepareStatement(sql);
            pst.setString(1, name);
            i += pst.executeUpdate();
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            pst = null;
            conn = null;
        }
        return i;
    }

    public int insertSongFinger(String name, ArrayList<ShazamHash> hashes) {
        Connection conn = getConn();
        int i = 0;
        short id = 0;
        String s = "'";
        if (name.contains(s))
            name = name.replace("'", "\\'");//处理歌名中的单引号
        String sql = "insert into songfinger(song_id,finger_f1,finger_f2,finger_dt,offSet,finger_id) values(?,?,?,?,?,?)";
        String select_id = "select id from song where name='" + name + "'";
        try {
            conn.setAutoCommit(false);
            Long beginTime = System.currentTimeMillis();
            pst = (PreparedStatement) conn.prepareStatement(select_id);
            ResultSet rs = pst.executeQuery();
            while (rs.next())
                id = rs.getShort(1);
            pst = (PreparedStatement) conn.prepareStatement(sql);
            for (int j = 0; j < hashes.size(); ++j) {
                pst.setShort(1, id);
                pst.setShort(2, hashes.get(j).f1);
                pst.setShort(3, hashes.get(j).f2);
                pst.setShort(4, hashes.get(j).dt);
                pst.setShort(5, hashes.get(j).offSet);
                pst.setInt(6, hashes.get(j).finger_id);
                i += pst.executeUpdate();
                pst.addBatch();
                //每1000次提交一次     
                /*if (j % 1500 == 0) {//可以设置不同的大小；如50，100，500，1000等等      
                    pst.executeBatch();
                    conn.commit();
                    pst.clearBatch();
                }*/
            }
            pst.executeBatch();
            conn.commit();
            pst.clearBatch();
            Long endTime = System.currentTimeMillis();
            System.out.println("pst+batch：" + (endTime - beginTime) / 1000 + "秒");
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            conn = null;
            pst = null;
        }
        return i;
    }

    public String getName(Short id) {
        Connection conn = getConn();
        String name = null;
        String select_name = "select name from song where id ='" + id + "'";
        try {
            pst = (PreparedStatement) conn.prepareStatement(select_name);
            ResultSet rs = pst.executeQuery();
            while (rs.next())
                name = rs.getString(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            conn = null;
            pst = null;
        }
        return name;
    }

    public Map<Short, List<Short>> getoffSet(Map<Short, List<Short>> id_hashes, ArrayList<ShazamHash> hashes) {
        List<Short> offSets;
        Connection conn = getConn();
        Map<Integer, List<Short>> fingerid_offSets = new HashMap<>();
        for (ShazamHash hash : hashes
                ) {
            if (fingerid_offSets.containsKey(hash.finger_id))
                fingerid_offSets.get(hash.finger_id).add(hash.offSet);
            else {
                offSets = new ArrayList<>();
                offSets.add(hash.offSet);
                fingerid_offSets.put(hash.finger_id, offSets);
            }
        }

        offSets = null;

        int i = 0;
        int index = 0;
        StringBuffer select_offSet = new StringBuffer();
        select_offSet.append("select song_id,offSet,finger_id from songfinger where finger_id in(");
        for (ShazamHash hash : hashes
                ) {
            if (hashes.indexOf(hash) != hashes.size() - 1)
                select_offSet.append(hash.finger_id + ",");
            else
                select_offSet.append(hash.finger_id);
        }
        select_offSet.append(")");

        try {
            Long beginTime = System.currentTimeMillis();
            conn.setAutoCommit(false);
            System.out.println("开始查找曲库");
            pst = (PreparedStatement) conn.prepareStatement(select_offSet.toString());

            ResultSet rs = pst.executeQuery();

            rs.last();
            int raw = rs.getRow();
            short[] id = new short[raw];
            int[] fingerid = new int[raw];
            rs.beforeFirst();
            while (rs.next()) {
                id[i] = rs.getShort("song_id");
                fingerid[i] = rs.getInt("finger_id");
                if (id_hashes.containsKey(id[i])) {
                    for (Short offSet : fingerid_offSets.get(fingerid[i])
                            ) {
                        id_hashes.get(id[i]).add((short) (rs.getShort("offSet") - offSet));
                    }
                } else {
                    offSets = new ArrayList<>();
                    for (Short offSet : fingerid_offSets.get(fingerid[i])
                            ) {
                        offSets.add((short) (rs.getShort("offSet") - offSet));
                    }
                    id_hashes.put(id[i], offSets);
                }
                i++;
            }
            pst.close();
            conn.close();
            Long endTime = System.currentTimeMillis();
            System.out.println("读取时长：" + (endTime - beginTime) / 1000f + "秒");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            conn = null;
            pst = null;
        }
        return id_hashes;
    }
}
